package com.zhiyou100.dao.Impl;

import com.zhiyou100.JDBCUtil.DBUtil;
import com.zhiyou100.dao.LesseeDao;
import com.zhiyou100.emtity.Lessee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @description:
 * @author: 马跃
 * @create: 2021-01-22 20:12
 **/
public class LesseeDaoImpl implements LesseeDao {

    private QueryRunner qur = new QueryRunner(DBUtil.getDataSource());
    @Override
    public List<Lessee> queryAll() {
        String sql = "select * from lessee";
        try {
            List<Lessee> query = qur.query(sql, new BeanListHandler<>(Lessee.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void add(Lessee lessee) {
        String sql = "insert into lessee values(null,?,?,?,?,?,?)";
        try {
            qur.update(sql,
                    lessee.getL_name(),
                    lessee.getL_phone(),
                    lessee.getL_sex(),
                    lessee.getL_nativePlace(),
                    lessee.getL_idNumber(),
                    new SimpleDateFormat("yyyy-MM-dd HH:ss:mm").format(new Date()));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public void delete(int id) {
        String sql = "delete from lessee where l_id = ?";
        try {
            qur.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public Lessee query(int id) {
        String sql = "SELECT l.*,h.h_address,h.h_id,c.c_id\n" +
                "FROM lessee l\n" +
                "JOIN contract c\n" +
                "ON c.l_id=c.l_id\n" +
                "JOIN house h\n" +
                "ON c.h_id=h.h_id\n" +
                "WHERE l.l_id=?\n" +
                "GROUP BY 1";
        try {
            Lessee query = qur.query(sql, new BeanHandler<>(Lessee.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void update(Lessee lessee) {
        String sql = "update lessee set\n " +
                "l_name=?,\n" +
                "l_phone=?,\n" +
                "l_sex=?,\n" +
                "l_nativePlace=?,\n" +
                "l_idNumber=?\n" +
                "where l_id=?";
        try {
            qur.update(sql, lessee.getL_name(),lessee.getL_phone(),lessee.getL_sex(),lessee.getL_nativePlace(),lessee.getL_idNumber(),lessee.getL_id());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }
}
